package cz.muni.fi.pv168.confusinglibrary.libs;

import cz.muni.fi.pv168.confusinglibrary.database.DBCall;
import cz.muni.fi.pv168.confusinglibrary.database.DBUtils;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Date;
import java.sql.ResultSet;
import java.util.List;
import javax.sql.DataSource;

/**
 * SQL code for table of Books.

CREATE TABLE app.book
(id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
author_id INTEGER,
name VARCHAR(100),
description LONG VARCHAR,
fiction SMALLINT,
published DATE
);
 */
public class BookManagerDB implements BookManager {
    private DataSource ds;
    AuthorManagerDB authorManager;

    public BookManagerDB() {
        authorManager = new AuthorManagerDB();
    }

    public void setDataSource(DataSource ds) {
        this.ds = ds;
        authorManager.setDataSource(ds);
    }


    @Override
    public Book addBook(Book book) {
        if (book == null) {
            throw new NullPointerException("book");
        }

        final Book finalBook = book;

        DBCall<Long> dbCall = new DBCall<Long>(ds) {
            @Override
            public Long makeCall() throws SQLException {
                
                PreparedStatement st = null;
                st = conn.prepareStatement("INSERT INTO APP.book (name,author_id,description,fiction,published)"
                        + " VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
                fillStatementFromBook(st, finalBook);

//                assert (st.executeUpdate() == 1);
                st.executeUpdate();

                return DBUtils.getId(st.getGeneratedKeys());
            }
        };
        
        book.setId(dbCall.call());
        return book;
    }

    @Override
    public void removeBook(Book book) {
        if (book == null) {
            throw new NullPointerException("book");
        }
        if (book.getId() == null) {
            throw new NullPointerException("book id");
        }

        final Book finalBook = book;

        DBCall<Void> dbCall = new DBCall<Void>(ds) {
            @Override
            public Void makeCall() throws SQLException {

                PreparedStatement st = null;
                st = conn.prepareStatement("DELETE FROM APP.book WHERE id = ?");
                st.setLong(1, finalBook.getId());

                if (st.executeUpdate() == 0) {
                    throw new IllegalArgumentException("Book doesn't exist");
                }
                return null;
            }
        };
        dbCall.call();
    }

    @Override
    public Book updateBook(Book book) {
        if (book == null) {
            throw new NullPointerException("book");
        }
        if (book.getId() == null) {
            throw new NullPointerException("book id");
        }

        final Book finalBook = book;

        DBCall<Void> dbCall = new DBCall<Void>(ds) {
            @Override
            public Void makeCall() throws SQLException {

                PreparedStatement st = null;
                st = conn.prepareStatement("UPDATE APP.book SET name = ?, author_id = ?, description = ?, "
                        + "fiction = ?, published = ? WHERE id = ?");

                fillStatementFromBook(st, finalBook);
                st.setLong(6, finalBook.getId());

                if (st.executeUpdate() == 0) {
                    throw new IllegalArgumentException("Book doesn't exist");
                }
                
                return null;
            }
        };
        dbCall.call();

        return book;
    }

    @Override
    public List<Book> getAllBooks() {
        DBCall<List<Book>> dbCall = new DBCall<List<Book>>(ds) {
            @Override
            public List<Book> makeCall() throws SQLException {

                PreparedStatement st = null;
                st = conn.prepareStatement("SELECT * from APP.book");

                ResultSet rs = st.executeQuery();
                List<Book> books = new ArrayList<Book>();

                while (rs.next()) {
                    books.add(bookFromResultSet(rs));
                }

                return books;
            }
        };

        return dbCall.call();
    }

    @Override
    public Book findBookById(final Long id) {
        if (id == null) {
            throw new IllegalArgumentException("Id can't be null");
        }

        DBCall<Book> dbCall = new DBCall<Book>(ds) {
            @Override
            public Book makeCall() throws SQLException {
                
                PreparedStatement st = null;
                st = conn.prepareStatement("SELECT * from APP.book WHERE id = ?");
                st.setLong(1, id);

                ResultSet rs = st.executeQuery();

                AuthorManager authorManager = new AuthorManagerDB();

                if (!rs.next()) {
                    throw new IllegalArgumentException("Book with this id doesn't exist");
                }
                
                return bookFromResultSet(rs);
            }
        };
        
        return dbCall.call();
    }

    @Override
    public List<Book> findBooksByName(final String name) {
        if (name == null) {
            throw new IllegalArgumentException("Name can't be null");
        }

        DBCall<List<Book>> dbCall = new DBCall<List<Book>>(ds) {
            @Override
            public List<Book> makeCall() throws SQLException {

                PreparedStatement st = null;
                st = conn.prepareStatement("SELECT * from APP.book WHERE name = ?");
                st.setString(1, name);

                ResultSet rs = st.executeQuery();
                List<Book> books = new ArrayList<Book>();

                while (rs.next()) {
                    books.add(bookFromResultSet(rs));
                }

                return books;
            }
        };

        return dbCall.call();
    }

    @Override
    public List<Book> findBooksByAuthor(final Author author) {
        if (author == null) {
            throw new IllegalArgumentException("Author can't be null");
        }
        if (author.getId() == null) {
            throw new IllegalArgumentException("Author can't have id set to null");
        }

        DBCall<List<Book>> dbCall = new DBCall<List<Book>>(ds) {
            @Override
            public List<Book> makeCall() throws SQLException {
                
                PreparedStatement st = null;
                st = conn.prepareStatement("SELECT * from APP.book WHERE author_id = ?");
                st.setLong(1, author.getId());

                ResultSet rs = st.executeQuery();
                List<Book> books = new ArrayList<Book>();

                while (rs.next()) {
                    books.add(bookFromResultSet(rs));
                }

                return books;
            }
        };

        return dbCall.call();
    }

    
    private Book bookFromResultSet(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getLong("id"));
        book.setName(rs.getString("name"));
        book.setAuthor(authorManager.findAuthorById(rs.getLong("author_id")));
        book.setDescription(rs.getString("description"));
        book.setFiction(rs.getBoolean("fiction"));
        book.setPublished(rs.getDate("published") != null ?
                new java.util.Date(rs.getDate("published").getTime()) : null);
        return book;
    }

    private void fillStatementFromBook(PreparedStatement st, Book book) throws SQLException {
        st.setString(1, book.getName());
        if (book.getAuthor() != null) {
            st.setLong(2, book.getAuthor().getId());
        } else {
            st.setNull(2, java.sql.Types.INTEGER);
        }
        st.setString(3, book.getDescription());
        st.setShort(4, (short) (book.isFiction() ? 1 : 0));
        st.setDate(5, book.getPublished() == null ? null : new Date(book.getPublished().getTime()));
    }
}
